package com.luoha.excel;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.luoha.lottery.Employee;


public class ExcelHandler {
    
    /**
     * read the Excel file
     * @param path the path of the Excel file
     * @return
     * @throws IOException
     */
	
    public List<Employee> readExcel(String path) throws IOException {
        if (path == null || ExcelUtil.EMPTY.equals(path)) {
            return null;
        } else {
            String postfix = ExcelUtil.getPostfix(path);
            if (!ExcelUtil.EMPTY.equals(postfix)) {
                if (ExcelUtil.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
                    return readXls(path);
                } else if (ExcelUtil.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
                    return readXlsx(path);
                }
            } else {
                System.out.println(path + ExcelUtil.NOT_EXCEL_FILE);
            }
        }
        return null;
    }
    /**
     * update the Excel file
     * @param path the path of the Excel file
     * @return
     * @throws IOException
     */
	
    public void updateExcel(String path,Employee employee) throws IOException {
        if (path == null || ExcelUtil.EMPTY.equals(path)) {
            return;
        } else {
            String postfix = ExcelUtil.getPostfix(path);
            if (!ExcelUtil.EMPTY.equals(postfix)) {
                if (ExcelUtil.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
                    updateXls(path,employee);
                } else if (ExcelUtil.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
                    updateXlsx(path,employee);
                }
            } else {
                System.out.println(path + ExcelUtil.NOT_EXCEL_FILE);
            }
        }
    }
    /**
     * Read the Excel 2010
     * @param path the path of the excel file
     * @return
     * @throws IOException
     */
    
    public List<Employee> readXlsx(String path) throws IOException {
        System.out.println(ExcelUtil.PROCESSING + path);
        InputStream is = new FileInputStream(path);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        Employee employee = null;
        List<Employee> list = new ArrayList<Employee>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) {
                	employee = new Employee();
                    XSSFCell empId = xssfRow.getCell(1);
                    XSSFCell empName = xssfRow.getCell(2);
                    employee.setEmpId(empId.toString());
                    employee.setEmpName(empName.toString());
                    list.add(employee);
                }
            }
        }
        return list;
    }
    /**
     * Read the Excel 2010
     * @param path the path of the excel file
     * @return
     * @throws IOException
     */
    
    public List<Employee> readExtractXlsx(String path) throws IOException {
        System.out.println(ExcelUtil.PROCESSING + path);
        InputStream is = new FileInputStream(path);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        Employee employee = null;
        List<Employee> list = new ArrayList<Employee>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) {
                	employee = new Employee();
                    XSSFCell empId = xssfRow.getCell(1);
                    XSSFCell empName = xssfRow.getCell(2);
                    XSSFCell lotteryId = xssfRow.getCell(3);
                    XSSFCell lotteryName = xssfRow.getCell(4);
                    String id = lotteryId.toString();
                    if(id != null && !id.equals("")){
                    	employee.setEmpId(empId.toString());
                        employee.setEmpName(empName.toString());
                        employee.setLotteryId(Integer.parseInt(id.split("\\.")[0]));
                        employee.setLotteryName(lotteryName.toString());
                        list.add(employee);
                    } 
                }
            }
        }
        return list;
    }
    

    /**
     * Read the Excel 2003-2007
     * @param path the path of the Excel
     * @return
     * @throws IOException
     */
    
    public List<Employee> readXls(String path) throws IOException {
        System.out.println(ExcelUtil.PROCESSING + path);
        InputStream is = new FileInputStream(path);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        Employee employee = null;
        List<Employee> list = new ArrayList<Employee>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                	employee = new Employee();
                    HSSFCell empId = hssfRow.getCell(1);
                    HSSFCell empName = hssfRow.getCell(2);
                    employee.setEmpId(empId.toString());
                    employee.setEmpName(empName.toString());
                    list.add(employee);
                }
            }
        }
        return list;
    }
    /**
     * Update the Excel 2010
     * @param path the path of the excel file
     * @return
     * @throws IOException
     */
    
    public void updateXlsx(String path,Employee employee) throws IOException {
        System.out.println(ExcelUtil.PROCESSING + path);
        String empId = employee.getEmpId();
        if(empId == null || empId.equals("")){
        	return;
        }
        InputStream is = new FileInputStream(path);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        List<Employee> list = new ArrayList<Employee>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) {
                    XSSFCell empId1 = xssfRow.getCell(1);
                    if(empId1 != null && empId.equals(empId1.toString())){
                    	xssfRow.getCell(3).setCellValue(employee.getLotteryId());
                    	xssfRow.getCell(4).setCellValue(employee.getLotteryName());
                    	break;
                    }
                }
            }
           
        }
        FileOutputStream out = new FileOutputStream(path);
        xssfWorkbook.write(out);
        
    }
    

    /**
     * Update the Excel 2003-2007
     * @param path the path of the Excel
     * @return
     * @throws IOException
     */
    
    public void updateXls(String path,Employee employee) throws IOException {
        System.out.println(ExcelUtil.PROCESSING + path);
        String empId = employee.getEmpId();
        if(empId == null || empId.equals("")){
        	return;
        }
        InputStream is = new FileInputStream(path);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                	HSSFCell empId1 = hssfRow.getCell(1);
                    if(empId1 != null && empId.equals(empId1.toString())){
                    	hssfRow.getCell(3).setCellValue(employee.getLotteryId());
                    	hssfRow.getCell(4).setCellValue(employee.getLotteryName());
                    	break;
                    }
                }
            }
        }
        FileOutputStream out = new FileOutputStream(path);
        hssfWorkbook.write(out);
    }
    
    
    public static void clearLottery(String path) throws IOException {
        System.out.println(ExcelUtil.PROCESSING + path);
        InputStream is = new FileInputStream(path);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        // Read the Sheet
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) {
                	xssfRow.getCell(3).setCellValue("");
                	xssfRow.getCell(4).setCellValue("");
                }
            }
        }
        FileOutputStream out = new FileOutputStream(path);
        xssfWorkbook.write(out);
    }

    @SuppressWarnings("static-access")
    private String getValue(XSSFCell xssfRow) {
        if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfRow.getBooleanCellValue());
        } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
            return String.valueOf(xssfRow.getNumericCellValue());
        } else {
            return String.valueOf(xssfRow.getStringCellValue());
        }
    }

    @SuppressWarnings("static-access")
    private String getValue(HSSFCell hssfCell) {
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(hssfCell.getNumericCellValue());
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }
    
    public static void main(String[] args){
    	Employee employee = new Employee();
    	employee.setEmpId("LHSH001");
    	employee.setEmpName("尹桑");
    	employee.setLotteryId(1);
    	employee.setLotteryName("奖品1");
    	ExcelHandler handler = new ExcelHandler();
    	try {
			handler.updateXlsx("C:\\Users\\AllenHao\\Desktop\\lottery\\员工名单.xlsx", employee);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    }
}
